import logging

from fastapi import APIRouter, Depends, Query
from pydantic import Field
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

user_scene_router = APIRouter(prefix="/scene/userScene", tags=["场景设定管理"], dependencies=[Depends(oauth2_scheme)])


class UserSceneSearch(PaginationBase):
    fsiteNo: str = Field(None, description="客户编号")
    fscenetype: str = Field(None, description="场景类型")


@user_scene_router.get("/list", summary="查询场景设定")
def list_user_scene(search: UserSceneSearch = Query(), db: Session = Depends(get_db)):
    try:
        with db.begin():

            param = {}
            sql = "select count(1) from s_tc_scene_head"
            sql = sql + " where 1=1"
            if search.fsiteNo is not None:
                sql = sql + " and fsite_no= :fsiteNo "
                param["fsiteNo"] = search.fsiteNo
            if search.fscenetype is not None:
                sql = sql + " and fscenetype= :fscenetype "
                param["fscenetype"] = search.fscenetype
            else:
                sql = sql + " and fscenetype >= 3"
            count = db.execute(text(sql), param).scalar()
            param = {}
            sql = "select fairitem,fexectime,fexecweek1,fexecweek2,fexecweek3,fexecweek4"
            sql = sql + ",fexecweek5,fexecweek6,fexecweek7,foperator,frange"
            sql = sql + ",fscenename,fsceneno,fscenetype,fsite_no,fteacherno"
            sql = sql + ",fupddate,fvalue,fvalue2,fwhere,fwhere2"
            sql = sql + " from s_tc_scene_head"
            sql = sql + " where 1=1"
            if search.fsiteNo is not None:
                sql = sql + " and fsite_no= :fsiteNo "
                param["fsiteNo"] = search.fsiteNo
            if search.fscenetype is not None:
                sql = sql + " and fscenetype= :fscenetype "
                param["fscenetype"] = search.fscenetype
            else:
                sql = sql + " and fscenetype >= 3"
            sql = sql + " limit :limit offset :offset "
            param["offset"] = search.offset
            param["limit"] = search.limit
            data = db.execute(text(sql), param).fetchall()

            # atsPoints 明细列表
            param = {}
            sql_3 = " select b.fentryid,b.fsceneno,b.fpoint_no fpointno "
            sql_3 = sql_3 + " ,c.fpoint_name fpointName"
            sql_3 = sql_3 + " from s_tc_scene_head a"
            sql_3 = sql_3 + " inner join s_tc_scene_point b on a.fsceneno=b.fsceneno"
            sql_3 = sql_3 + " left join a_ts_point c on b.fpoint_no=c.fpoint_no"
            sql_3 = sql_3 + " where 1=1 "
            if search.fsiteNo is not None:
                sql_3 = sql_3 + " and a.fsite_no= :fsiteNo "
                param["fsiteNo"] = search.fsiteNo
            if search.fscenetype is not None:
                sql_3 = sql_3 + " and a.fscenetype= :fscenetype "
                param["fscenetype"] = search.fscenetype
            sql_3 = sql_3 + " order by b.fsceneno,b.fentryid"
            data_detail = db.execute(text(sql_3), param).fetchall()

            # sTsEquipmentList 明细列表
            param = {}
            sql_2 = " select b.faction,b.factiontype,b.fentryid,b.fequipmentbarcode"
            sql_2 = sql_2 + ",b.feuipmentid,c.fdictname feuipmentname,b.finstructions,b.fposition,b.fsceneno"
            sql_2 = sql_2 + " from s_tc_scene_head a,s_tc_scene_detail b,s_ts_equipment_dict c "
            sql_2 = sql_2 + " where a.fsceneno=b.fsceneno "
            sql_2 = sql_2 + " and b.feuipmentid=c.fdictno"
            if search.fsiteNo is not None:
                sql_2 = sql_2 + " and a.fsite_no= :fsiteNo "
            if search.fscenetype is not None:
                sql_2 = sql_2 + " and a.fscenetype= :fscenetype "
            sql_2 = sql_2 + " order by b.fsceneno,b.fentryid"
            data_detail2 = db.execute(text(sql_2), param).fetchall()

            data_list = []
            for key in range(len(data)):
                d1 = {}
                d1['executed'] = "执行中"
                d1['fairitem'] = data[key][0]
                d1['fexectime'] = data[key][1]
                d1['fexecweek1'] = data[key][2]
                d1['fexecweek2'] = data[key][3]
                d1['fexecweek3'] = data[key][4]
                d1['fexecweek4'] = data[key][5]
                d1['fexecweek5'] = data[key][6]
                d1['fexecweek6'] = data[key][7]
                d1['fexecweek7'] = data[key][8]
                d1['foperator'] = data[key][9]
                d1['frange'] = data[key][10]
                d1['fscenename'] = data[key][11]
                d1['fsceneno'] = data[key][12]
                d1['fscenetype'] = data[key][13]
                d1['fsiteNo'] = data[key][14]
                d1['fteacherno'] = data[key][15]
                d1['fupddate'] = data[key][16]
                d1['fvalue'] = data[key][17]
                d1['fvalue2'] = data[key][18]
                d1['fwhere'] = data[key][19]
                d1['fwhere2'] = data[key][20]
                d1['fpointName'] = None
                d1['fpointNo'] = None
                d1['isExecuted'] = ""
                sbillno = data[key][12]
                weekList = list()
                if data[key][2] == "1":
                    weekList.append("1")
                if data[key][3] == "1":
                    weekList.append("2")
                if data[key][4] == "1":
                    weekList.append("3")
                if data[key][5] == "1":
                    weekList.append("4")
                if data[key][6] == "1":
                    weekList.append("5")
                if data[key][7] == "1":
                    weekList.append("6")
                if data[key][8] == "1":
                    weekList.append("7")
                # print(weekList)
                d1['fexecweekList'] = weekList
                atsPoints = []
                for j1 in range(len(data_detail)):
                    if (str(data_detail[j1][1]) == str(sbillno)):
                        d2 = {}
                        d2['fentryid'] = data_detail[j1][0]
                        d2['fsceneno'] = data_detail[j1][1]
                        d2['fpointNo'] = data_detail[j1][2]
                        d2['fpointName'] = data_detail[j1][3]
                        atsPoints.append(d2)

                sTsEquipmentList = []
                for j2 in range(len(data_detail2)):
                    if (str(data_detail2[j2][8]) == str(sbillno)):
                        d3 = {}
                        d3['faction'] = data_detail2[j2][0]
                        d3['factiontype'] = data_detail2[j2][1]
                        d3['fentryid'] = data_detail2[j2][2]
                        d3['fequipmentbarcode'] = data_detail2[j2][3]
                        d3['fequipmenttag'] = None
                        d3['feuipmentid'] = data_detail2[j2][4]
                        d3['feuipmentname'] = data_detail2[j2][5]
                        d3['fimage'] = None
                        d3['finstructions'] = data_detail2[j2][6]
                        d3['fposition'] = data_detail2[j2][7]
                        d3['fsceneno'] = data_detail2[j2][8]
                        sTsEquipmentList.append(d3)

                d1["atsPoints"] = atsPoints
                d1["sTsEquipmentList"] = sTsEquipmentList
                sTcSceneItemList = []
                d1["sTcSceneItemList"] = sTcSceneItemList
                sTsEquipmentList2 = []
                d1["sTsEquipmentList2"] = sTsEquipmentList2
                data_list.append(d1)

            info_dict = {"list": data_list,
                         "limit": search.limit,
                         "page": search.pageIndex,
                         "pages": (count + search.limit - 1) // search.limit,
                         "total": count}
            return BaseResponse.success(data=info_dict)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
